﻿using BusinessRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace BusinessRule.XCCZ
{
  public  class xccz
    {
        /// <summary>
        ///  资产报废基础数据
        /// </summary>
        /// <param name="gsdm"></param>
        /// <param name="time1"></param>
        /// <returns></returns>
        public static async Task<int> InsertZCBF(string gsdm, DateTime time1)
        {
            string  time2 = time1.ToString("yyyyMM");
            //---本期报废明细表
            string sql1 = " begin delete fa_dbb;  ";
            string sql2 = " insert into fa_dbb select a.fatype_code,case when SUBSTR(fatype_code,1,4)='1029' then '103'  ";
            sql2 += " when SUBSTR(fatype_code,1,5)='20109' then '710'  else b.lbdm end as lbdm, ";
            sql2 += " case when SUBSTR(fatype_code,1,4)='1029' then '构筑物' ";
            sql2 += " when SUBSTR(fatype_code,1,5)='20109' then '计算机软件' else b.lbmc end as lbmc, ";
            sql2 += " case when SUBSTR(fatype_code,1,4)='1029' then '8' ";
            sql2 += " when SUBSTR(fatype_code,1,5)='20109' then '6'  else to_char(b.yjnx) end as yjnx ";
            sql2 += " from (select distinct fatype_code from fa_card) a left join (select * from fa_zjnx) b ";
            sql2 += " on SUBSTR(fatype_code,1,3)=b.lbdm  where SUBSTR(fatype_code,1,1) in  ('1','2','3','4','5','6') ";
            sql2 += " union select fatype_code,'710' as lbdm,'计算机软件' lbmc,'6' as yjnx ";
            sql2 += " from (select distinct fatype_code from fa_card) where SUBSTR(fatype_code,1,1) not in  ('1','2','3','4','5','6'); ";
            //card_id 卡片id(主键) co_code单位代码 fatype_code资产代码 cost原值 fastart_date 开始使用日期
            string sql3 = " delete fa_zxmx where co_code='"+gsdm+"';  ";
            sql3 += " insert into fa_zxmx select card_id,co_code,a.fatype_code,a.fa_name,cost as yz,'0' as yzje,'0' as ljzj, ";
            sql3 += " '0' as jz,to_char(fastart_date,'mm/dd/yyyy') as ksrq,b.yjnx, ";
            sql3 += " case when ceil(months_between(to_date('"+ time2 + "','yyyy-mm'),";
            sql3 += " to_date(to_char(fastart_date,'mm/yyyy'),'mm/yyyy'))) < b.yjnx*12 then ";
            sql3 += "  ceil(months_between(to_date('"+ time2 + "','yyyy-mm'), ";
            sql3 += " to_date(to_char(fastart_date,'mm/yyyy'),'mm/yyyy'))) else b.yjnx*12 end as yyyf, ";
            sql3 += " '0' as syyf from fa_card a left join (select * from fa_dbb) b ";
            sql3 += " on a.fatype_code=b.fatype_code where a.co_code='"+gsdm+"' and to_date(to_char(close_date,'mm/yyyy'),'mm/yyyy')=to_date('"+ time2 + "','yyyy-mm') ";
            sql3 += " order by card_id; ";
            string sql4 = " update fa_zxmx set syyf=yjnx*12-yyyf where co_code='"+gsdm+"';  ";
            //--处理累计折旧
            string sql5 = " update fa_zxmx set ljzj=(select ljzj from(select case when  ";
            sql5 += " ceil(months_between(to_date('"+ time2 + "','yyyy-mm'),to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))<yjnx*12 and yjnx<>'0' then (round(YZ/YJNX/12,2))*yyyf";
            sql5 += " when ceil(months_between(to_date('"+ time2 + "','yyyy-mm'),to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))=yjnx*12 and yjnx<>'0' then yz";
            sql5 += " when yjnx='0' then 0 else yz end as ljzj,card_id  from fa_zxmx) where card_id=fa_zxmx.card_id) where co_code='"+gsdm+"'; ";
            //--处理净值
            string sql6 = " update fa_zxmx set jz=yz-ljzj where co_code='"+gsdm+"';  end ;";
            string sql111 = sql1 + sql2 + sql3 + sql4 + sql5 + sql6;


             return await new RepositoryFactory().XcczOrcaleRepository().ExecuteSql(sql1+sql2+sql3+sql4+sql5+sql6);
        }

        /// <summary>
        /// 查询报废数据
        /// </summary>
        /// <param name="gsdm"></param>
        /// <param name="time1"></param>
        /// <returns></returns>
        public static async Task<DataTable> selectZCBF(string gsdm, DateTime time1)
        {
            //返回数据
            string sql7 = "  select * from (select co_code,xh,fatype_code,sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz from";
            sql7 += " (select co_code,case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,1) else '7' end as xh,";
            sql7 += " case when SUBSTR(fatype_code,1,1)='1' then '土地、房屋及构筑物' when SUBSTR(fatype_code,1,1)='2' and SUBSTR(fatype_code,1,5)<>'20109' then '通用设备'";
            sql7 += "  when SUBSTR(fatype_code,1,1)='3' then '专用设备' when SUBSTR(fatype_code,1,1)='4' then '文物和陈列品'";
            sql7 += "  when SUBSTR(fatype_code,1,1)='5' then '图书、档案'  when SUBSTR(fatype_code,1,1)='6' then '家具、用具、装具及动植物'";
            sql7 += " else '无形资产' end as fatype_code,yz,ljzj,yzje,jz from fa_zxmx where co_code='" + gsdm + "') a";
            sql7 += " group by fatype_code,xh,co_code union select co_code,xh,fatype_code,sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz from(";
            sql7 += " select co_code,'0' as xh,'合计' as fatype_code,yz,ljzj,yzje,jz from fa_zxmx";
            sql7 += " where co_code='" + gsdm + "')a group by fatype_code,xh,co_code union";
            sql7 += " select co_code,xh,b.lbmc,yz,ljzj,yzje,jz from ( select co_code,";
            sql7 += " case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,3) else '710' end as xh,";
            sql7 += " sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz from fa_zxmx";
            sql7 += " where co_code='" + gsdm + "' group by co_code,case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,3) else '710' end) a left join";
            sql7 += " (select * from fa_zjnx)b on a.xh = b.lbdm order by xh)";
            return await new RepositoryFactory().XcczOrcaleRepository().FindTable(sql7,null);
        }
        /// <summary>
        /// 资产购买基础数据
        /// </summary>
        /// <param name="gsdm"></param>
        /// <param name="time1"></param>
        /// <returns></returns>
        public static async Task<int> InsertZCGM(string gsdm, DateTime time1)
        {
            string time2 = time1.ToString("yyyyMM");
            string sql1 = " begin  delete fa_dbb;";
            string sql2 = " insert into fa_dbb select a.fatype_code,case when SUBSTR(fatype_code,1,4)='1029' then '103'";
            sql2 += " when SUBSTR(fatype_code,1,5)='20109' then '710'  else b.lbdm end as lbdm,";
            sql2 += " case when SUBSTR(fatype_code,1,4)='1029' then '构筑物' when SUBSTR(fatype_code,1,5)='20109' then '计算机软件' else b.lbmc end as lbmc,";
            sql2 += " case when SUBSTR(fatype_code,1,4)='1029' then '8' when SUBSTR(fatype_code,1,5)='20109' then '6'  else to_char(b.yjnx) end as yjnx";
            sql2 += " from (select distinct fatype_code from fa_card) a left join (select * from fa_zjnx) b";
            sql2 += " on SUBSTR(fatype_code,1,3)=b.lbdm where SUBSTR(fatype_code,1,1) in  ('1','2','3','4','5','6') ";
            sql2 += " union select fatype_code,'710' as lbdm,'计算机软件' lbmc,'6' as yjnx from (select distinct fatype_code from fa_card)";
            sql2 += " where SUBSTR(fatype_code,1,1) not in  ('1','2','3','4','5','6');";
            string sql3 = " delete fa_gmmx where co_code='"+gsdm+"';";
            string sql4 = " insert into fa_gmmx select card_id,co_code,a.fatype_code,a.fa_name,cost as yz,'0' as yzje,'0' as ljzj,'0' as jz,to_char(fastart_date,'mm/dd/yyyy') as ksrq,b.yjnx,";
            sql4 += " case when ceil(months_between(to_date('"+time2+"','yyyy/mm'),to_date(to_char(fastart_date,'mm/yyyy'),'mm/yyyy')))+1 < b.yjnx*12 then";
            sql4 += " ceil(months_between(to_date('"+ time2 + "','yyyy/mm'),to_date(to_char(fastart_date,'mm/yyyy'),'mm/yyyy')))+1";
            sql4 += " else b.yjnx*12 end as yyyf,'0' as syyf from fa_card a left join (select * from fa_dbb)b";
            sql4 += " on a.fatype_code=b.fatype_code where a.co_code='"+gsdm+ "'  and to_char(fastart_date,'yyyymm')<=to_char(to_date('" + time2 + "','yyyymm'),'yyyymm')";
            sql4 += " and nvl(to_char(close_date,'yyyymm'),999999)>to_char(to_date('" + time2 + "','yyyymm'),'yyyymm')  and to_date(to_char(input_date,'mm/yyyy'),'mm/yyyy')=to_date('" + time2 + "','yyyy/mm')";
            sql4 += " order by card_id;";
            ///处理月折金额
            string sql5 = " update fa_gmmx set syyf=yjnx*12-yyyf where co_code='"+gsdm+"';";
            sql5 += " update fa_gmmx set yzje=(select yzje from(select case when ceil(months_between(to_date('"+ time2 + "','yyyy/mm'),";
            sql5 += " to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1=yjnx*12 then yz-(round(YZ/YJNX/12,2)*(yyyf-1))";
            sql5 += " when ceil(months_between(to_date('"+ time2 + "','yyyy/mm'),";
            sql5 += " to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1<yjnx*12 then (round(YZ/YJNX/12,2))";
            sql5 += " when yjnx='0' then 0";
            sql5 += " else 0 end as yzje,card_id from fa_gmmx) where card_id=fa_gmmx.card_id) where co_code='"+gsdm+"';";
            //--处理累计折旧
            string sql6 = " update fa_gmmx set ljzj=(select ljzj from(select case when ceil(months_between(to_date('"+ time2 + "','yyyy/mm'),";
            sql6 += " to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1<yjnx*12 and yjnx<>'0' then yzje*yyyf";
            sql6 += "  when ceil(months_between(to_date('"+ time2 + "','yyyy/mm'),";
            sql6 += "  to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1=yjnx*12 and yjnx<>'0' then yz";
            sql6 += " when yjnx='0' then 0 else yz end as ljzj,card_id";
            sql6 += " from fa_gmmx) where card_id=fa_gmmx.card_id) where co_code='"+gsdm+"';";
            //--处理净值
            string sql7 = " update fa_gmmx set jz=yz-ljzj where co_code='"+gsdm+"'; end;";
            string sqlZ = sql1 + sql2 + sql3 + sql4 + sql5 + sql6 + sql7;
            return await new RepositoryFactory().XcczOrcaleRepository().ExecuteSql(sqlZ);
        }

        /// <summary>
        ///  查看资产购置数据
        /// </summary>
        /// <param name="gsdm"></param>
        /// <param name="time1"></param>
        /// <returns></returns>
        public static async Task<DataTable> selectZCGM(string gsdm, DateTime time1)
        {
            string sql = " select * from ( select co_code,xh,fatype_code,sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz from";
            sql += " (select co_code,case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,1) else '7' end as xh,";
            sql += " case when SUBSTR(fatype_code,1,1)='1' then '土地、房屋及构筑物'";
            sql += " when SUBSTR(fatype_code,1,1)='2' and SUBSTR(fatype_code,1,5)<>'20109' then '通用设备'";
            sql += " when SUBSTR(fatype_code,1,1)='3' then '专用设备'";
            sql += " when SUBSTR(fatype_code,1,1)='4' then '文物和陈列品'";
            sql += " when SUBSTR(fatype_code,1,1)='5' then '图书、档案'";
            sql += " when SUBSTR(fatype_code,1,1)='6' then '家具、用具、装具及动植物'";
            sql += "  else '无形资产' end as fatype_code,yz,ljzj,yzje,jz ";
            sql += " from fa_gmmx where co_code='"+gsdm+"') a group by fatype_code,xh,co_code";
            sql += " union";
            sql += " select co_code,xh,fatype_code,sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz from(";
            sql += " select co_code,'0' as xh,'合计' as fatype_code,yz,ljzj,yzje,jz from fa_gmmx";
            sql += " where co_code='"+gsdm+"')a group by fatype_code,xh,co_code";
            sql += " union";
            sql += " select co_code,xh,b.lbmc,yz,ljzj,yzje,jz from (select co_code, ";
            sql += " case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,3) else '710' end as xh,";
            sql += " sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz";
            sql += " from fa_gmmx where co_code='"+gsdm+"'";
            sql += " group by co_code,case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,3) else '710' end) a left join";
            sql += " (select * from fa_zjnx)b on a.xh = b.lbdm ) c order by xh";
            return await new RepositoryFactory().XcczOrcaleRepository().FindTable(sql, null);
        }

        /// <summary>
        ///  资产折旧汇总表 基础数据
        /// </summary>
        /// <param name="gsdm"></param>
        /// <param name="time1"></param>
        /// <returns></returns>
        public static async Task<int> InsertZJPZ(string gsdm, DateTime time1)
        {
            string time2 = time1.ToString("yyyyMM");
            string sql = "  begin delete fa_dbb; insert into fa_dbb";
            sql += " select a.fatype_code,case when SUBSTR(fatype_code,1,4)='1029' then '103'";
            sql += " when SUBSTR(fatype_code,1,5)='20109' then '710'  else b.lbdm end as lbdm,";
            sql += " case when SUBSTR(fatype_code,1,4)='1029' then '构筑物'";
            sql += " when SUBSTR(fatype_code,1,5)='20109' then '计算机软件' else b.lbmc end as lbmc,";
            sql += " case when SUBSTR(fatype_code,1,4)='1029' then '8'";
            sql += " when SUBSTR(fatype_code,1,5)='20109' then '6'  else to_char(b.yjnx) end as yjnx";
            sql += " from (select distinct fatype_code from fa_card) a left join (select * from fa_zjnx) b";
            sql += " on SUBSTR(fatype_code,1,3)=b.lbdm where SUBSTR(fatype_code,1,1) in  ('1','2','3','4','5','6')";
            sql += " union select fatype_code,'710' as lbdm,'计算机软件' lbmc,'6' as yjnx";
            sql += " from (select distinct fatype_code from fa_card) where SUBSTR(fatype_code,1,1) not in  ('1','2','3','4','5','6');";
            string sql1 = " delete fa_zjmx where co_code='"+gsdm+"'; insert into fa_zjmx";
            sql1 += " select card_id,co_code,a.fatype_code,a.fa_name,cost as yz,'0' as yzje,'0' as ljzj,'0' as jz,to_char(fastart_date,'mm/dd/yyyy') as ksrq,b.yjnx,";
            sql1 += " case when ceil(months_between(to_date('"+time2+"','yyyy/mm'),to_date(to_char(fastart_date,'mm/yyyy'),'mm/yyyy')))+1 < b.yjnx*12 then";
            sql1 += " ceil(months_between(to_date('"+time2+"','yyyy/mm'),to_date(to_char(fastart_date,'mm/yyyy'),'mm/yyyy')))+1";
            sql1 += " else b.yjnx*12 end as yyyf,'0' as syyf from fa_card a left join (select * from fa_dbb)b on a.fatype_code=b.fatype_code";
            sql1 += " where a.co_code='"+gsdm+ "'  and to_char(fastart_date,'yyyymm')<=to_char(to_date('" + time2 + "','yyyymm'),'yyyymm')  and nvl(to_char(close_date,'yyyymm'),999999)>to_char(to_date('" + time2 + "','yyyymm'),'yyyymm')";
            sql1 += " and to_date(to_char(input_date,'mm/yyyy'),'mm/yyyy')<=to_date('"+time2+"','yyyy/mm') order by card_id;";
            string sql2 = " update fa_zjmx set syyf=yjnx*12-yyyf where co_code='"+gsdm+"';";
            //--处理月折金额
            string sql3 = " update fa_zjmx set yzje=(select yzje from(select case when ceil(months_between(to_date('"+time2+"','yyyy/mm'),";
            sql3 += " to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1=yjnx*12 then yz-(round(YZ/YJNX/12,2)*(yyyf-1))";
            sql3 += " when ceil(months_between(to_date('"+time2+"','yyyy/mm'),";
            sql3 += " to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1<yjnx*12 then (round(YZ/YJNX/12,2))";
            sql3 += "  when yjnx='0' then 0  else 0 end as yzje,card_id from fa_zjmx) where card_id=fa_zjmx.card_id) where co_code='"+gsdm+"';";
            //--处理累计折旧
            string sql4 = " update fa_zjmx set ljzj=(select ljzj from(select case when ceil(months_between(to_date('"+time2+"','yyyymm'),";
            sql4 += " to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1<yjnx*12 and yjnx<>'0' then yzje*yyyf";
            sql4 += "  when ceil(months_between(to_date('"+time2+"','yyyy/mm'),";
            sql4 += "    to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1=yjnx*12 and yjnx<>'0' then yz";
            sql4 += " when yjnx='0' then 0 else yz end as ljzj,card_id";
            sql4 += " from fa_zjmx) where card_id=fa_zjmx.card_id) where co_code='"+gsdm+"';";
            //--处理净值
            string sql5 = "update fa_zjmx set jz=yz-ljzj where co_code='"+gsdm+"';";
            //最后要展示的表1(折旧明细表)
            string sql6 = " delete fa_cpz where co_code='"+gsdm+"'; insert into fa_cpz";
            sql6 += " select co_code,xh,fatype_code,sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz from";
            sql6 += " (select co_code,case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,1) else '7' end as xh,";
            sql6 += "  case when SUBSTR(fatype_code,1,1)='1' then '土地、房屋及构筑物'";
            sql6 += "   when SUBSTR(fatype_code,1,1)='2' and SUBSTR(fatype_code,1,5)<>'20109' then '通用设备'";
            sql6 += "   when SUBSTR(fatype_code,1,1)='3' then '专用设备' when SUBSTR(fatype_code,1,1)='4' then '文物和陈列品'";
            sql6 += "  when SUBSTR(fatype_code,1,1)='5' then '图书、档案'  when SUBSTR(fatype_code,1,1)='6' then '家具、用具、装具及动植物'";
            sql6 += "  else '无形资产' end as fatype_code,yz,ljzj,yzje,jz from fa_zjmx where co_code='"+gsdm+"') a";
            sql6 += " group by fatype_code,xh,co_code  union";
            sql6 += " select co_code,xh,fatype_code,sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz from(";
            sql6 += " select co_code,'0' as xh,'合计' as fatype_code,yz,ljzj,yzje,jz from fa_zjmx where co_code='"+gsdm+"') a";
            sql6 += " group by fatype_code,xh,co_code  order by to_number(xh);commit; end;";
            string sql11 = sql + sql1 + sql2 + sql3 + sql4 + sql5 + sql6;
            return await new RepositoryFactory().XcczOrcaleRepository().ExecuteSql(sql11);
        }

        /// <summary>
        /// 查看折旧汇总表
        /// </summary>
        /// <param name="gsdm"></param>
        /// <param name="time1"></param>
        /// <returns></returns>
        public static async Task<DataTable> selectZJPZ(string gsdm, DateTime time1)
        {
            string sql = "  select * from ( select * from fa_cpz where co_code='"+gsdm+"' union select co_code,xh,b.lbmc,yz,ljzj,yzje,jz from (";
            sql += " select co_code,case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,3) else '710' end as xh,";
            sql += " sum(yz) as yz,sum(ljzj) as ljzj,sum(yzje) as yzje,sum(jz) as jz";
            sql += " from fa_zjmx where co_code='"+gsdm+"' group by co_code,case when SUBSTR(fatype_code,1,5)<>'20109' then SUBSTR(fatype_code,1,3) else '710' end) a";
            sql += "  left join (select * from fa_zjnx)b on a.xh = b.lbdm ) c order by xh ";
            
             return await new RepositoryFactory().XcczOrcaleRepository().FindTable(sql, null);
          
        }

        public static async Task<DataTable> GetZJNX()
        {
            string sql = "select * from FA_ZJNX order by lbdm ";
            return await new RepositoryFactory().XcczOrcaleRepository().FindTable(sql, null);

        }
        /// <summary>
        /// 资产折旧明细基础数据
        /// </summary>
        /// <param name="gsdm"></param>
        /// <param name="time1"></param>
        /// <returns></returns>
        public static async Task<int> InsertZCZJMX(string gsdm, DateTime time1)
        {
            string time2 = time1.ToString("yyyyMM");
            string sql = " begin  delete fa_dbb;insert into fa_dbb ";
            string sql1 = " select a.fatype_code,case when SUBSTR(fatype_code,1,4)='1029' then '103'";
            sql1 += " when SUBSTR(fatype_code,1,5)='20109' then '710'  else b.lbdm end as lbdm,case when SUBSTR(fatype_code,1,4)='1029' then '构筑物'";
            sql1 += "  when SUBSTR(fatype_code,1,5)='20109' then '计算机软件' else b.lbmc end as lbmc,case when SUBSTR(fatype_code,1,4)='1029' then '8' ";
            sql1 += " when SUBSTR(fatype_code,1,5)='20109' then '6'  else to_char(b.yjnx) end as yjnx from (select distinct fatype_code from fa_card) a";
            sql1 += " left join (select * from fa_zjnx) b on SUBSTR(fatype_code,1,3)=b.lbdm where SUBSTR(fatype_code,1,1) in  ('1','2','3','4','5','6')";
            sql1 += " union select fatype_code,'710' as lbdm,'计算机软件' lbmc,'6' as yjnx  from (select distinct fatype_code from fa_card)";
            sql1 += " where SUBSTR(fatype_code,1,1) not in  ('1','2','3','4','5','6');";
            string sql2 = " delete fa_zjmx where co_code='"+gsdm+"';insert into fa_zjmx";
            sql2 += " select card_id,co_code,a.fatype_code,a.fa_name,cost as yz,'0' as yzje,'0' as ljzj,'0' as jz,to_char(fastart_date,'mm/dd/yyyy') as ksrq,b.yjnx,";
            sql2 += " case when ceil(months_between(to_date('"+time2+"','yyyy/mm'),to_date(to_char(fastart_date,'mm/yyyy'),'mm/yyyy')))+1 < b.yjnx*12 then";
            sql2 += " ceil(months_between(to_date('"+time2+"','yyyy/mm'),to_date(to_char(fastart_date,'mm/yyyy'),'mm/yyyy')))+1";
            sql2 += " else b.yjnx*12 end as yyyf,'0' as syyf from fa_card a left join (select * from fa_dbb)b on a.fatype_code=b.fatype_code";
            sql2 += " where a.co_code='"+gsdm+ "' and to_char(fastart_date,'yyyymm')<=to_char(to_date('" + time2 + "','yyyymm'),'yyyymm') and nvl(to_char(close_date,'yyyymm'),999999)>to_char(to_date('" + time2 + "','yyyymm'),'yyyymm')";
            sql2 += " and to_date(to_char(input_date,'mm/yyyy'),'mm/yyyy')<=to_date('"+time2+"','yyyy/mm') order by card_id;";
            string sql3 = " update fa_zjmx set syyf=yjnx*12-yyyf where co_code='"+gsdm+"';";
            //--处理月折金额
            string sql4 = " update fa_zjmx set yzje=(select yzje from(select case when ceil(months_between(to_date('"+time2+"','yyyy/mm'),";
            sql4 += " to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1=yjnx*12 then yz-(round(YZ/YJNX/12,2)*(yyyf-1))";
            sql4 += "  when ceil(months_between(to_date('"+time2+"','yyyy/mm'),to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1<yjnx*12 then (round(YZ/YJNX/12,2))";
            sql4 += " when yjnx='0' then 0  else 0 end as yzje,card_id from fa_zjmx) where card_id=fa_zjmx.card_id) where co_code='"+gsdm+"';";
            //--处理累计折旧
            string sql5 = " update fa_zjmx set ljzj=(select ljzj from(select case when ceil(months_between(to_date('"+time2+"','yyyy/mm'),";
            sql5 += " to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1<yjnx*12 and yjnx<>'0' then yzje*yyyf";
            sql5 += "  when ceil(months_between(to_date('"+time2+"','yyyy/mm'),to_date(to_char(to_date(ksrq,'mm/dd/yyyy'),'mm/yyyy'),'mm/yyyy')))+1=yjnx*12 and yjnx<>'0' then yz";
            sql5 += " when yjnx='0' then 0 else yz end as ljzj,card_id from fa_zjmx) where card_id=fa_zjmx.card_id) where co_code='"+gsdm+"';";
            //--处理净值
            string sql6 = " update fa_zjmx set jz=yz-ljzj where co_code='"+gsdm+"' ; end;";
            string sqlz = sql + sql1 + sql2 + sql3 + sql4 + sql5 + sql6;
            return await new RepositoryFactory().XcczOrcaleRepository().ExecuteSql(sqlz);
        }
        public static async Task<DataTable> selectZCZJMX(string gsdm, DateTime time1,string lbdm)
        {
            string sql = " select card_id,co_code,fatype_code,fa_name,yz,yzje,ljzj,jz,ksrq,yjnx,yyyf,syyf, SUBSTR(fatype_code,1,3) as lbdm from fa_zjmx where co_code='"+gsdm+ "' and SUBSTR(fatype_code,1,3)='" + lbdm + "' order by card_id";
            return await new RepositoryFactory().XcczOrcaleRepository().FindTable(sql, null);
        }
    }
}
